package cn.com.tuns.assess.qsb.frame.util;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;
import cn.com.tuns.assess.qsb.frame.ui.AppManage;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * sqlite数据库的操作通用处理
 *
 */
public class DBUtil {
    // 数据库文件名
    private String dbFile = null;
    // 数据库路径
    private String dbPath = null;
    // 数据库
    private SQLiteDatabase db = null;

    private boolean autoClose = false;
    /**
     * 用Toast提示SQL错了
     */
    private boolean isShowErrorSqlToast = true;

    /**
     * 构建函数,确省本地数据库文件为/sdcard/unisoft/db/local.db
     *
     * @param path
     * @param dbFile
     */
    public DBUtil(String path, String dbFile) {
        this.dbFile = dbFile;
        this.dbPath = path;

        if (this.dbFile == null)
            this.dbFile = "local.db";
        if (this.dbPath == null)
            this.dbPath = AppManage.getRootPath() + "/db";
    }

    /**
     * 构建函数,确省本地数据库文件为/sdcard/unisoft/db/local.db
     *
     * @param pathAnddbFile
     */
    public DBUtil(String pathAnddbFile) {
        int lastIdx = pathAnddbFile.lastIndexOf("/");
        String dbFile = pathAnddbFile.substring(lastIdx + 1);
        String path = pathAnddbFile.substring(0, lastIdx);
        this.dbFile = dbFile;
        this.dbPath = path;

        if (this.dbFile == null)
            this.dbFile = "local.db";
        if (this.dbPath == null)
            this.dbPath =AppManage.getRootPath() + "/db";
    }

    public static String getInsertSQL(String tabName, String[] colNames) {
        String insql = "INSERT INTO " + tabName + " (";
        String vs = "";
        for (int i = 0; i < colNames.length; i++) {
            if (i > 0) {
                insql += " , ";
                vs += ",";
            }
            insql += colNames[i];
            vs += "?";
        }
        insql += ") values(";
        insql += vs; // ?,?,?,?
        insql += ")";

        return insql;
    }

    public static String getInsertSQL(String tabName, String[] colNames, String[] colValue) {
        String insql = "INSERT INTO " + tabName + " (";
        String vs = "";
        for (int i = 0; i < colNames.length; i++) {
            if (i > 0) {
                insql += " , ";
                vs += ",";
            }
            insql += colNames[i];
            vs += "'" + colValue[i] + "'";
        }
        insql += ") values(";
        insql += vs; // ?,?,?,?
        insql += ")";

        return insql;
    }

    public static String getUpdateSql(String tabName, String keyField,
                                      String keyValue, String[] colNames) {
        String updStr = "UPDATE " + tabName + " SET ";
        for (int i = 0; i < colNames.length; i++) {
            if (i > 0)
                updStr += " , ";
            updStr += (colNames[i] + " = ?");
        }
        // +
        updStr += (" WHERE " + keyField + "='" + keyValue + "'");
        return updStr;
    }

    public static String getUpdateSql(String tabName, String keyField, String[] colNames) {
        String updStr = "UPDATE " + tabName + " SET ";
        for (int i = 0; i < colNames.length; i++) {
            if (i > 0)
                updStr += " , ";
            updStr += (colNames[i] + " = ?");
        }
        // +
        updStr += (" WHERE " + keyField + "=?");
        return updStr;
    }

    /**
     * 给查询语句增加模糊搜索的字段
     *
     * @param sql     原始SQL
     * @param colName 需要搜索的列名
     * @param value   值
     */
    public static String addSearchEnd(String sql, String[] colName,
                                      String[] value) {
        StringBuffer sb = new StringBuffer(sql);
        if (colName != null && colName.length > 0) {
            sb.append(" and (");
            for (int i = 0; i < colName.length; i++) {
                if (i > 0)
                    sb.append(" or");
                sb.append(" ");
                sb.append(colName[i]);
                sb.append(" like '%");
                sb.append(value[i]);
                sb.append("%'");
            }
            sb.append(")");
        }
        return sb.toString();
    }

    public void setShowErrorSqlToast(boolean showErrorSqlToast) {
        isShowErrorSqlToast = showErrorSqlToast;
    }

    // 打开数据库，返回SQLiteDatabase对象
    public SQLiteDatabase openDB() {
        if (db != null)
            return db;
        if (AppManage.setDB.equals(this.dbFile)) {
            SQLiteDatabase db2 = AppManage.dbSys();
            db = db2;
            return db;
        } else if (AppManage.userDB.equals(this.dbFile)) {
            SQLiteDatabase db2 = AppManage.dbUser();
            db = db2;
            return db;
        }
        File file = new File(this.dbPath + "/" + this.dbFile);
        // 因为本地数据库都是COPY的，所以不能自己创建数据库
        if (!file.exists()) {
//            try {
//                throw new RuntimeException("出问题！");
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//            System.out.println("本地数据库出现错误。" + this.dbPath + "/" + this.dbFile);
//            AppManager.showToastMessage(WSApplication.context.getString(AppR
//                    .getString("commlib_error_db")));
            return null;
        }
        try {
            db = SQLiteDatabase.openOrCreateDatabase(file, null);
        } catch (Exception ex) {
            ex.printStackTrace();
            LogUtil.saveCrashInfo2File(ex);

            db = null;
        }
        return db;
    }

    /**
     * 关闭数据
     */
    public void close() {
//		if (db != null)
//			db.close();
//		db = null;
    }

    public void setAutoClose(boolean bClose) {
        this.autoClose = bClose;
    }

    /**
     * 处理数据库异常
     *
     * @param e
     */
    private void doSqlException(SQLiteException e) {
        try {
            Log.d("system=DBUtil", this.dbPath + "=" + this.dbFile);
            e.printStackTrace();

            LogUtil.saveCrashInfo2File(e);
        } catch (Exception ee) {
            ee.printStackTrace();
        }
    }

    /*
     * 查询：根据主建查询某个字段数据值，可用于设置
     */
    public String getByKeyString(String tabName, String keyField,
                                 String keyValue, String outField) {
        // 打开数据库
        if (this.openDB() == null)
            return null;

        String retString = null;
        try {
            Cursor cursor = db.query(tabName, new String[]{outField},
                    keyField + "=?", new String[]{keyValue}, null, null,
                    null, null);

            // mCursor不等于null,将标识指向第一条记录
            if (cursor != null) {
                cursor.moveToFirst();
                if (cursor.getCount() > 0) {
                    cursor.moveToPosition(0);
                    retString = cursor.getString(0);
                }
                cursor.close();
            }
        } catch (SQLiteException e) // 异常
        {
            doSqlException(e);
            return null;
        }
        if (this.autoClose)
            this.close();

        return retString;
    }

    /*
     * 查询：根据条件查询多个字段数据值，返回Cursor 注意不会自动关闭，即使autoClose＝true
     */
    public Cursor getCursorByAddition(String tabName, String[] addiFields,
                                      String[] addiValues, String[] outFields, String orderby) {
        // 打开数据库
        if (this.openDB() == null)
            return null;

        // 组合条件字符串
        String addiStr = null;
        if (addiFields != null) {
            addiStr = "";
            for (int i = 0; i < addiFields.length; i++) {
                if (i > 0)
                    addiStr += " and ";
                addiStr += (addiFields[i] + "=?");
            }
        }

        try {
            Cursor cursor = db.query(tabName, outFields, addiStr, addiValues,
                    null, null, orderby, null);

            return cursor;
        } catch (SQLiteException e) // 异常
        {
            doSqlException(e);
            return null;
        }
    }

    /**
     * 通过原始的SQL获取 注意不会自动关闭，即使autoClose＝true
     *
     * @param sql
     * @param params
     * @return
     */
    public synchronized Cursor getRawSql(String sql, String[] params) {
        // 打开数据库
        if (this.openDB() == null)
            return null;

        try {
            Cursor cursor = db.rawQuery(sql, params);

            return cursor;
        } catch (SQLiteException e) // 异常
        {
            doSqlException(e);
            return null;
        }
    }

    /**
     * 查询：根据条件查询多个字段数据值,返回字段数据数组列表
     *
     * @param tabName
     * @param addiFields
     * @param addiValues
     * @param outFields
     * @param orderby
     * @return
     */
    public List<String[]> getFieldsByAddition(String tabName,
                                              String[] addiFields, String[] addiValues, String[] outFields,
                                              String orderby) {
        Cursor cursor = this.getCursorByAddition(tabName, addiFields,
                addiValues, outFields, orderby);
        // mCursor不等于null,将标识指向第一条记录
        if (cursor == null)
            return null;

        int colCnt = outFields.length;
        List<String[]> retList = new ArrayList<String[]>();
        for (int row = 0; row < cursor.getCount(); row++) {
            cursor.moveToPosition(row);
            String[] values = new String[colCnt];
            // 获取字段数据
            for (int col = 0; col < colCnt; col++) {
                values[col] = cursor.getString(col);
                if (values[col] == null)
                    values[col] = "";
            }
            retList.add(values);
        }
        cursor.close();

        if (this.autoClose)
            this.close();
        return retList;
    }

    /**
     * 查询：根据条件查询多个字段数据值,返回字段数据数组列表
     *
     * @param sql
     * @param sqlArgs
     * @return
     */
    public List<HashMap<String, Object>> getFieldsBySql(String sql, String[] sqlArgs) {
        List<HashMap<String, Object>> retList = new ArrayList<HashMap<String, Object>>();
        // 打开数据库
        if (this.openDB() == null)
            return null;
        try {
            Cursor cursor = db.rawQuery(sql, sqlArgs);
            // mCursor不等于null,将标识指向第一条记录
            if (cursor == null)
                return null;

            for (int row = 0; row < cursor.getCount(); row++) {
                cursor.moveToPosition(row);
                HashMap<String, Object> rowData = new HashMap<String, Object>();
                // 获取字段数据
                for (int col = 0; col < cursor.getColumnCount(); col++) {
                    String key = cursor.getColumnName(col);
                    int type = cursor.getType(col);
                    String value = cursor.getString(col);
                    if (value == null || value.length() == 0) {
                        continue;
                    }
                    if (Cursor.FIELD_TYPE_STRING == type) {
                        rowData.put(key, cursor.getString(col));
                    } else if (Cursor.FIELD_TYPE_INTEGER == type) {
                        rowData.put(key, cursor.getInt(col));
                    } else if (Cursor.FIELD_TYPE_FLOAT == type) {
                        rowData.put(key, cursor.getDouble(col));
                    } else {
                        rowData.put(key, cursor.getString(col));
                    }
                }
                retList.add(rowData);
            }
            cursor.close();
        } catch (SQLiteException e) // 异常
        {
            doSqlException(e);
        } catch (Exception ee) // 异常
        {
            ee.printStackTrace();
        }
        if (this.autoClose)
            this.close();
        return retList;
    }

    /**
     * 查询表的数据
     *
     * @param tabName   　表名称
     * @param outFields 　需要查询的数据字段数组
     * @param orderby   　排序字符串 order by colname
     * @return
     */
    public List<String[]> getFieldsByTable(String tabName, String[] outFields,
                                           String orderby) {
        return this
                .getFieldsByAddition(tabName, null, null, outFields, orderby);
    }

    /**
     * 查询表的数据
     *
     * @param tabName   　表名称
     * @param outFields 　需要查询的数据字段数组
     * @param orderby   　排序字符串 order by colname
     * @return
     */
    public String getFieldStrByTable(String tabName, String[] outFields,
                                     String orderby) {
        StringBuffer sb = new StringBuffer();
        List<String[]> result = getFieldsByAddition(tabName, null, null,
                outFields, orderby);
        for (int i = 0; i < result.size(); i++) {
            sb.append(result.get(i) + ",");
        }
        sb.setLength(sb.length() - 1);
        return sb.toString();
    }

    /**
     * 根据主键修改某个字段
     *
     * @param tabName
     * @param keyField
     * @param keyValue
     * @param colName
     * @param colValue
     * @return
     */
    public boolean updateColByKey(String tabName, String keyField,
                                  String keyValue, String colName, Object colValue) {
        // 打开数据库
        if (this.openDB() == null)
            return false;

        String updStr = "UPDATE " + tabName + " SET " + colName + "=? WHERE "
                + keyField + "=?";
        Object[] parms = new Object[]{colValue, keyValue};

        try {
            db.execSQL(updStr, parms);
        } catch (SQLiteException e) // 异常
        {
            doSqlException(e);
            return false;
        }

        if (this.autoClose)
            this.close();

        return true;
    }

    /**
     * 根据主键修改某个字段
     *
     * @param tabName
     * @param keyField
     * @param keyValue
     * @param colNames
     * @param colValues
     * @return
     */
    public boolean updateColsByKey(String tabName, String keyField,
                                   String keyValue, String[] colNames, Object[] colValues) {
        // 打开数据库
        if (this.openDB() == null)
            return false;

        Object[] values = new Object[colNames.length + 1];
        String updStr = "UPDATE " + tabName + " SET ";
        for (int i = 0; i < colNames.length; i++) {
            if (i > 0)
                updStr += " , ";
            updStr += (colNames[i] + " = ?");
            values[i] = colValues[i];
        }
        // +
        updStr += (" WHERE " + keyField + "=?");
        values[colNames.length] = keyValue;
        try {
            db.execSQL(updStr, values);
        } catch (SQLiteException e) // 异常
        {
            doSqlException(e);
            return false;
        }

        if (this.autoClose)
            this.close();

        return true;
    }

    /**
     * 从某个表增加单条数据
     */
    public boolean insertRow(String tabName, String[] colNames,
                             Object[] colValues) {
        // 打开数据库
        if (this.openDB() == null)
            return false;

        String insql = getInsertSQL(tabName, colNames);

        try {
            db.execSQL(insql, colValues);
        } catch (SQLiteException e) {
            doSqlException(e);
            return false;
        }
        if (this.autoClose)
            this.close();
        return true;
    }

    /**
     * 从某个表增加多条数据
     */
    public boolean insertRows(String tabName, String[] colNames,
                              List<Object[]> colValues) {
        // 打开数据库
        if (this.openDB() == null)
            return false;

        String insql = this.getInsertSQL(tabName, colNames);
        Object[] tempVals = null;
        try {
            for (Object[] vals : colValues) {
                tempVals = vals;
                db.execSQL(insql, vals);
            }
        } catch (SQLiteException e) {
            String errSql = "错误的SQL=" + insql;
            for (Object obj : tempVals) {
                errSql += "'" + obj + "',";
            }
            System.out.println("错误的SQL=" + errSql);
            doSqlException(e);
            return false;
        }

        if (this.autoClose)
            this.close();
        return true;
    }

    /**
     * 删除表所有数据
     *
     * @param tableName 表名
     */
    public void deleTable(String tableName) {
        // 打开数据库
        if (this.openDB() == null)
            return;

        String sql = "delete from " + tableName;
        try {
            db.execSQL(sql);
        } catch (SQLiteException e) {
            doSqlException(e);
            return;
        }

        if (this.autoClose)
            this.close();
    }

    /**
     * 根据条件删除表中的数据
     *
     * @param tableName 表名
     */
    public void deleTable(String tableName, String keyField, String keyValue) {
        // 打开数据库
        if (this.openDB() == null)
            return;

        String sql = "delete from " + tableName + " where " + keyField + "= ?";
        try {
            db.execSQL(sql, new Object[]{keyValue});
        } catch (SQLiteException e) {
            doSqlException(e);
            return;
        }

        if (this.autoClose)
            this.close();
    }

    /**
     * 运行Sql语言
     *
     * @param sql
     */
    public void execSQL(String sql) {
        execSQL(sql, null);
    }

    /**
     * 运行Sql语言
     *
     * @param sql
     * @param parms
     */
    public synchronized boolean execSQL(String sql, Object[] parms) {
        // 打开数据库
        if (this.openDB() == null)
            return false;
        try {
            if (parms == null)
                db.execSQL(sql);
            else
                db.execSQL(sql, parms);
        } catch (SQLiteException e) {
            System.out.println("错误ERROR SQL:" + sql);
            doSqlException(e);
            return false;
        }

        if (this.autoClose)
            this.close();
        return true;
    }

    /**
     * 开始一个事物
     */
    public void beginTransaction() {
        // 打开数据库
        if (this.openDB() == null)
            return;

        db.beginTransaction();
    }

    /**
     * 结束一个事物
     */
    public void endTransaction() {
        if (db == null)
            return;
        // 设置事物成功标记
        db.setTransactionSuccessful();
        db.endTransaction();
    }

    /**
     * 结束一个事物
     */
    public void endTransactionError() {
        if (db == null)
            return;
        db.endTransaction();
    }

    /**
     * 插入数据
     *
     * @param table
     * @param nullColumnHack
     * @param values
     */
    public long insert(String table, String nullColumnHack, ContentValues values) {
        // 打开数据库
        if (this.openDB() == null)
            return 0;
        return db.insert(table, nullColumnHack, values);
    }

    /**
     * 插入数据
     *
     * @param table
     * @param nullColumnHack
     * @param values
     */
    public long insertWithOnConflict(String table, String nullColumnHack, ContentValues values) {
        // 打开数据库
        if (this.openDB() == null)
            return 0;
        return db.insertWithOnConflict(table, nullColumnHack, values, SQLiteDatabase.CONFLICT_REPLACE);
    }


    /**
     * 得到表中所有的字段
     *
     * @param tableName
     * @return
     */
    public List<String> getTableCol( String tableName) {
        List<String> cols = new ArrayList<String>();
        Cursor cursor = getRawSql("PRAGMA table_info("+tableName+")",
                null);
        if (cursor != null) {
            cursor.moveToFirst();
            while (!cursor.isAfterLast()) {
                cols.add(StrUtil.nullToStr(cursor.getString(cursor.getColumnIndex("name"))));
                cursor.moveToNext();
            }
            cursor.close();
        }
        return cols;
    }
}
